<?

require_once '../../config/config.php';
// require the PHPExcel file 
require 'classes/PHPExcel.php';

$sql = 'SELECT id, id_etablissement, id_edition, id_abonnement, nb_beneficiares, date_creation, etat, commentaire FROM commande WHERE offre_essai = 0';
$headings = array('N°', 'Date de création', 'Etablissement', 'Ville', 'Adresse', 'Code postal', 'Contact administratif', 'Fonction', 'Téléphone', "E-mail", "Edition", 'Nombre de bénéficiaires', 'Statut', 'Connu le site');

$cellNumber = sizeof($headings);
$result = mysql_query($sql) or die(mysql_error());
// Create a new PHPExcel object 
$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->setTitle('Liste des visiteurs');

$styleArray = array(
    'font' => array(
        'bold' => true
    )
);

$rowNumber = 1;
$col = 'A';
foreach ($headings as $heading) {
    $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $heading);
    $objPHPExcel->getActiveSheet()->getStyle($col . $rowNumber)->applyFromArray($styleArray);
    $col++;
}

// Loop through the result set 
$rowNumber = 2;
while ($res = mysql_fetch_array($result)) {

    $id_commande = $res['id'];
    $date_creation = date('d-m-Y H:i', strtotime($res['date_creation']));

//# etablissement
    $id_etablisssment = $res['id_etablissement'];
    $sql_etablissement = "SELECT etablissement, ville, adresse, code_postal, connaissance FROM etablissement WHERE id = " . $id_etablisssment;
    $req_etablissement = mysql_query($sql_etablissement);
    $res_etablissement = mysql_fetch_array($req_etablissement);

    $etablissement = $res_etablissement['etablissement'];
    $ville = $res_etablissement['ville'];
    $adresse = $res_etablissement['adresse'];
    $code_postal = $res_etablissement['code_postal'];

    $commentaire = $res['commentaire'];

//# connaissance
    switch ($res_etablissement['connaissance']) {
        case 1:
            $connaissance = "Par un moteur de recherche";
            break;
        case 2:
            $connaissance = "Par un abonné";
            break;
        case 3:
            $connaissance = "Par les réseaux sociaux";
            break;
        case 4:
            $connaissance = "Par la presse";
            break;
        case 5:
            $connaissance = "Par un emailing";
            break;
        case 6:
            $connaissance = "Par un contact avec l’équipe de l’Essentiel du sup";
            break;
        case 7:
            $connaissance = "Autre";
            break;
        default :
            $connaissance = "-";
            break;
    }

//# contact administrative
    $sql_contact = "SELECT nom, prenom, fonction, telephone, email FROM contact_administratif WHERE id_etablissement = " . $id_etablisssment;
    $req_contact = mysql_query($sql_contact);
    $res_contact = mysql_fetch_array($req_contact);

    $nom_contact = ucwords($res_contact['nom']);
    $prenom_contact = ucwords($res_contact['prenom']);
    $fonction_contact = $res_contact['fonction'];
    $tel_contact = $res_contact['telephone'];
    $email_contact = $res_contact['email'];

//# edition
    switch ($res['id_edition']) {
        case 1:
            $edition = "Ecoles de management";
            break;
        case 2:
            $edition = "Ecoles d’ingénieurs";
            break;
        case 3:
            $edition = "Universités";
            break;
    }

//# nb beneficiares
    $sql_beneficiare = "SELECT id FROM beneficiare WHERE id_commande = " . $id_commande;
    $req_beneficiare = mysql_query($sql_beneficiare);
    $nb_beneficiares = mysql_num_rows($req_beneficiare);

//# statut
    switch ($res['etat']) {
        case 0:
            $statut = "En cours";
            break;
        case 1:
            $statut = "Activé";
            break;
        case 2:
            $statut = "Désactivé";
            break;
        default:
            break;
    }


    $info['0'] = $id_commande;
    $info['1'] = $date_creation;
    $info['2'] = $etablissement;
    $info['3'] = $ville;
    $info['4'] = $adresse;
    $info['5'] = $code_postal;
    $info['6'] = $nom_contact . ' ' . $prenom_contact;
    $info['7'] = $fonction_contact;
    $info['8'] = $tel_contact;
    $info['9'] = $email_contact;
    $info['10'] = $edition;
    $info['11'] = $nb_beneficiares;
    $info['12'] = $statut;
    $info['13'] = $connaissance;

    $col = 'A';
    foreach ($info as $cell) {
        $objPHPExcel->getActiveSheet()->setCellValue($col . $rowNumber, $cell);
        $col++;
    }
    $rowNumber++;
}

$objPHPExcel->getActiveSheet()->getStyle('A1:N' . $rowNumber)->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('A1:N' . $rowNumber)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);
for ($i = 0; $i <= $cellNumber; $i++) {
    $objPHPExcel->getActiveSheet()->getColumnDimension(chr(65 + $i))->setAutoSize(true);
}

// Save as an Excel BIFF (xls) file 
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$file_name = 'Liste des abonnes_' . strftime("%d-%m-%Y-%HH:%M", time()) . '.xls';

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $file_name . '"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
exit();
?>